第4話 Agile 匯出 Part & BOM (2)

本篇講解如何匯出BOM表
主料BOM表要匯出的表格如下
父階料號 | 子階料號 | 序號 | 用量 | Alternative | reference_designator|cn_bom_note
------------- | -------------

select
        item_par.item_number source_id, ---父階料號
        bom.item_number related_id, ---子階料號
        bom.find_number sort_order, ---序號
        NVL(bom.quantity,'1') quantity,  ---用量
        NVL(bom.text01,' ') alternative, ---替代料順序
        (select  rtrim(xmlagg(xmlelement(e, label, ',')).extract('//text()').getclobval(), ',') 
            from refdesig  r 
            where r.bom = bom.id) reference_designator ---組成用量
        ,bom.text02 cn_attrition_rate ---自訂損耗率
        ,( select  f.text
            from agile_flex f 
            where f.row_id = bom.id and f.attid =1036
        ) cn_bom_note ---備註
from bom bom
inner join item item_par on item_par.id = bom.item
inner join rev rev_p on rev_p.item = bom.item
inner join nodetable nb on nb.id = item_par.subclass
where
NVL (bom.change_out, 0) = 0
AND (SUBSTR (bom.flags, 5, 1) = 1)
AND (bom.text01 is null OR Trim(bom.text01)= 'R1')
AND rev_p.latest_flag = 1
AND rev_p.release_date >= TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND rev_p.release_date <= TO_DATE('2008-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
order by bom.item,lpad( bom.find_number, 3 )

• 各個欄位如何搜索可參考Select，這裡說明Where條件

BOM替代料匯出

select
        item_par.item_number source_id,
        (
            select bom.item_number from bom 
            where
            NVL (bom.change_out, 0) = 0
            AND (SUBSTR (bom.flags, 5, 1) = 1)
            AND bom.item = alterBOM.item
            AND bom.find_number = alterbom.find_number
            AND bom.text01 = 'R1'
        ) main_part,
        alterBOM.item_number related_id,
        alterBOM.find_number sort_order,
        to_number(SPLITSTR(alterBOM.text01,2,'R'))-1   alterantive,
        NVL(alterbom.quantity,'1') quantity
        ,alterBOM.text02 cn_attrition_rate
        ,( select  f.text
            from agile_flex f 
            where f.row_id = alterBOM.id and f.attid =1036
        ) cn_bom_note
    from bom alterBOM
    inner join item item_par on item_par.id = alterBOM.item
    inner join rev rev_p on rev_p.item = alterBOM.item
       inner join nodetable nb on nb.id = item_par.subclass
    where
    NVL (alterBOM.change_out, 0) = 0
    AND (SUBSTR (alterBOM.flags, 5, 1) = 1)
    AND alterbom.text01 <> 'R1'
    AND alterbom.text01 is not null
    AND rev_p.release_date >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND rev_p.release_date <= TO_DATE('2009-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    AND rev_p.LATEST_FLAG = 1 
    order by item_par.item_number,lpad( alterBOM.find_number, 3 )

• Where 條件的Alternative必須跳過R1、空白
• to_number(SPLITSTR(alterBOM.text01,2,'R'))-1 計算R的順序

可參考其他文章說明
https://www.cnblogs.com/namelessmyth/p/13042193.html